package cn.ac.cintcm.synonymous;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import oracle.jdbc.OraclePreparedStatement;

public class NameFieldDao {
    public static final String CATEGORY = "syn_category";
    public static final String SOURCE = "syn_source";
    private static final int BATCH_SIZE = 50;
    //最大名称长度
    public static final int MAX_NAME_SIZE = 20;
    private String table;
    private HashMap<String, NameField> nameMap;
    private HashMap<Short, NameField> idMap;
    private Connection conn;
    
    public NameFieldDao(Connection conn, String table) {
        this.table = table;
        this.conn = conn;
    }
    
    static void dropTable(Connection conn, String table){
        String sql1 = "drop table " + table;
        String sql2 = "drop sequence seq_" + table;
        Statement stmt;
        try {
            stmt = conn.createStatement();
            stmt.execute(sql1);
            stmt.execute(sql2);
            stmt.close();
        } catch (SQLException e) {
        }       
    }
    
    static void createTable(Connection conn, String table) throws SQLException {
        Statement stmt = conn.createStatement();   
        StringBuilder sql = new StringBuilder();
        sql.append("create table "+table+" (id number primary key, name varchar2(" + (MAX_NAME_SIZE * 2) +") not null)");
        stmt.execute(sql.toString());
        sql.setLength(0);
        sql.append("create sequence seq_" + table + " minvalue 1 start with 1 increment by 1");
        stmt.execute(sql.toString());
        sql.setLength(0);
        sql.append("create trigger trig_"+table+" before insert on " + table + " for each row begin \n");
        sql.append("select seq_"+ table +".nextval into :new.ID from dual;");
        sql.append("end;");
        stmt.execute(sql.toString());
        stmt.close();
    }
    
    public List<NameField> getAll() throws SQLException {
        String sql = "select * from " + table;
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery(sql);
        List<NameField> list = new ArrayList<NameField>();
        while (rset.next()) {
            short id = rset.getShort(1);
            String name = rset.getString(2);
            NameField nf = new NameField(id, name);
            list.add(nf);
        }
        rset.close();
        stmt.close();
        return list;
    }
    
    public void save(NameField nf) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("insert into " + table +
        " values(?, ?)" );
        try {
            ps.setInt(1, nf.getId());
            ps.setString(2, nf.getName());
            ps.executeUpdate();
        } catch (SQLException e) {
            ErrorData.write("field", nf.getName());
        }
        ps.close();
    }
    
    public void saveAll(List<NameField> list) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("insert into " + table +
        " values(?, ?)" );
        ((OraclePreparedStatement) ps).setExecuteBatch(BATCH_SIZE);
        for (NameField nf : list) {
            try {
                ps.setInt(1, nf.getId());
                ps.setString(2, nf.getName());
                ps.executeUpdate();
            } catch (SQLException e) {
                ErrorData.write("field", nf.getName());
            }
        }
        ps.close();       
    }
    
    public NameField getNameField(String name) {
        if (nameMap == null) {
            nameMap = new HashMap<String, NameField>();
            
            try {
                List<NameField> names = getAll();
                for (NameField n : names) {
                    nameMap.put(n.getName(), n);
                }
           } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return nameMap.get(name);
    }
    
    public NameField getNameField(short id) {
        if (idMap == null) {
            idMap = new HashMap<Short, NameField>();
            
            try {
                List<NameField> names = getAll();
                for (NameField n : names) {
                    idMap.put(n.getId(), n);
                }
           } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return idMap.get(id);
    }
}
